package com.bookcycle.panda.file.controller;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Controller
@RequestMapping("excel")
public class ExcelController extends BaseController{
    private Logger logger= LoggerFactory.getLogger(this.getClass());

    @RequestMapping(value = "/test",method = RequestMethod.GET)
    public String test(){
        return "excelTest";
    }
//
//
    @RequestMapping(value = "/exportExcel",method = RequestMethod.GET)
    public HttpServletResponse  exportExcel(HttpServletResponse response,
                                            @RequestParam("jsonArrayObject")String jsonArrayObject ){
        JSONArray jsonArray=JSONArray.fromObject(jsonArrayObject);
        JSONObject jsonObject=JSONObject.fromObject(jsonArray.get(0));
        int rows=jsonArray.size();
        int cols=jsonObject.size();
        try {
            XSSFWorkbook wb = new XSSFWorkbook();
            XSSFSheet sheet = wb.createSheet("数据");
            XSSFRow row = sheet.createRow(0);
            XSSFCellStyle style = wb.createCellStyle();

            Iterator iterator=jsonObject.keys();
            List<String> titles=new ArrayList<String>();
            int col=0;
            while (iterator.hasNext()){
                Object o=iterator.next();
                XSSFCell cell = row.createCell(col++); //第一个单元格
                titles.add(o.toString());
                cell.setCellValue(o.toString());
                cell.setCellStyle(style);
            }

            for (int i = 0; i < rows; i++) {
                row = sheet.createRow(i+1);
                JSONObject jo=jsonArray.getJSONObject(i);
                for(int j=0;j<cols;j++){
                    row.createCell(j).setCellValue(jo.get(titles.get(j)).toString());
                }
            }

            FileOutputStream fout = new FileOutputStream("temp.xlsx");
            wb.write(fout);
            fout.close();

            File file=new File("temp.xlsx");
            String filename = file.getName();
            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
            InputStream fis = new BufferedInputStream(new FileInputStream("temp.xlsx"));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            response.reset();
            response.setContentType("application/x-msdownload");
            OutputStream out =response.getOutputStream();
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
            file.delete();
        }catch (FileNotFoundException e){

        }catch (IOException e){

        }
        return response;
    }


    @RequestMapping(value = "/getListFromXLSX",method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<String> getListFromXLSX(@RequestParam("excel")MultipartFile excel){
        File temp=new File("temp.xlsx");
        JSONArray result=new JSONArray();
        List<String> title=new ArrayList<>();

        try {
            temp.createNewFile();
            FileOutputStream fileOutputStream=new FileOutputStream(temp);
            fileOutputStream.write(excel.getBytes());
            fileOutputStream.flush();
            fileOutputStream.close();
            XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(temp));
            XSSFSheet sheet0=wb.getSheetAt(0);
            XSSFRow row0=sheet0.getRow(0);

            //确认元素名称，第一行必须是名称，而不是数据
            for(int i=0;i<row0.getPhysicalNumberOfCells();i++){
                XSSFCell cell=(XSSFCell)row0.getCell(i);
                title.add(cell.toString());
            }
            logger.info(""+sheet0.getLastRowNum());
            for(int i=1;i<=sheet0.getLastRowNum();i++){
                XSSFRow row=(XSSFRow) sheet0.getRow(i);
                JSONObject o=new JSONObject();
                int length=row0.getPhysicalNumberOfCells();
                for (int j=0;j<length;j++)
                {
                    XSSFCell cell=(XSSFCell) row.getCell(j);
                    if(cell!=null){
                        String value=cell.toString();
                        o.element(title.get(j),value);
                    }

                }
                result.add(o);
            }
            temp.delete();
        }catch (IOException e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }catch (OfficeXmlFileException e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return ResponseEntity.status(HttpStatus.OK).body(result.toString());
    }

    @RequestMapping(value = "/getListFromXLS",method = RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<String> getListFromXLS(@RequestParam("excel")MultipartFile excel){
        List list=new ArrayList();
        File temp=new File("temp.xls");
        JSONArray result=new JSONArray();
        List<String> title=new ArrayList<>();

        try {
            temp.createNewFile();
            FileOutputStream fileOutputStream=new FileOutputStream(temp);
            fileOutputStream.write(excel.getBytes());
            fileOutputStream.flush();
            fileOutputStream.close();
            HSSFWorkbook wb=new HSSFWorkbook(new FileInputStream(temp));
            HSSFSheet sheet0=wb.getSheetAt(0);
            HSSFRow row0=sheet0.getRow(0);

            //确认元素名称，第一行必须是名称，而不是数据
            for(int i=0;i<row0.getPhysicalNumberOfCells();i++){
                HSSFCell cell=(HSSFCell)row0.getCell(i);
                title.add(cell.toString());
            }

            for(int i=1;i<=sheet0.getLastRowNum();i++){
                HSSFRow row=(HSSFRow) sheet0.getRow(i);
                JSONObject o=new JSONObject();
                int length=row0.getPhysicalNumberOfCells();
                for (int j=0;j<length;j++)
                {
                    HSSFCell cell=(HSSFCell) row.getCell(j);
                    if(cell!=null){
                        String value=cell.toString();
                        o.element(title.get(j),value);
                    }
                }
                result.add(o);
            }
            temp.delete();
        }catch (IOException e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }catch (OfficeXmlFileException e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return ResponseEntity.status(HttpStatus.OK).body(result.toString());
    }
}
